﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;


public class DAL_tbl_purchase_order
{

    public DAL_tbl_purchase_order()
    {

    }
    
    public List<tbl_purchase_order> select(int PO_ID)
    {
        List<tbl_purchase_order> list = new List<tbl_purchase_order>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select PO_ID,USER_CODE,PURCHASE_ORDER_NO,IS_SAMPLE,SUB_PRODUCT_ID,Currency_ID,QUANTITY,PRICE_PER_KG,PAYMENT_TERM_ID,DELIVERY_DATE,PARTIAL_PAYMENT,SHIPPING_ID,SHIPPING_TERMS_ID,PRODUCTION_RELESE_DATE,CREATED_DATE,CREATED_BY,ACCEPTED_BY,ACCEPTED_DATE,UPDATED_BY,UPDATED_DATE from tbl_purchase_order where PO_ID=@PO_ID";
        cmd.Parameters.AddWithValue("@PO_ID", PO_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_purchase_order()
            {
                PO_ID = Convert.ToInt32(dr["PO_ID"]),
                USER_CODE = dr["USER_CODE"].ToString(),
                PURCHASE_ORDER_NO = dr["PURCHASE_ORDER_NO"].ToString(),
                IS_SAMPLE = Convert.ToInt32(dr["IS_SAMPLE"]),
                SUB_PRODUCT_ID = Convert.ToInt32(dr["SUB_PRODUCT_ID"]),
                QUANTITY = Convert.ToInt32(dr["QUANTITY"]),
                PRICE_PER_KG = Convert.ToInt32(dr["PRICE_PER_KG"]),
                PAYMENT_TERM_ID = Convert.ToInt32(dr["PAYMENT_TERM_ID"]),
                DELIVERY_DATE = dr["DELIVERY_DATE"].toDateTimeWithoutError(),
                PARTIAL_PAYMENT = Convert.ToInt32(dr["PARTIAL_PAYMENT"]),
                SHIPPING_ID = Convert.ToInt32(dr["SHIPPING_ID"]),
                SHIPPING_TERMS_ID = Convert.ToInt32(dr["SHIPPING_TERMS_ID"]),
                PRODUCTION_RELESE_DATE = dr["PRODUCTION_RELESE_DATE"].toDateTimeWithoutError(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                ACCEPTED_BY = dr["ACCEPTED_BY"].ToString(),
                ACCEPTED_DATE = dr["ACCEPTED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public List<tbl_purchase_order> selectOrderNumber(string Po_number)
    {
        List<tbl_purchase_order> list = new List<tbl_purchase_order>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "SELECT CONCAT(CLIENTS.USER_CODE,'-', CASE WHEN ORDER_NO IS NULL "
                + " THEN 1 ELSE ORDER_NO + 1 END) ORDER_NO,PO_ID "
                + " FROM tbl_purchase_order CLIENTS "
                + " LEFT JOIN  "
                + " ("
                + " SELECT COUNT(1) AS  ORDER_NO ,TPO.USER_CODE "
                + " FROM TBL_RAW_MATERIAL_ORDER TRMO  "
                + " JOIN tbl_purchase_order TPO ON TPO.PO_ID = TRMO.PO_ID "
                + "GROUP BY USER_CODE "
                + " ) ORDERS ON CLIENTS.USER_CODE = ORDERS.USER_CODE "
                + " WHERE PURCHASE_ORDER_NO='" + Po_number + "'";
        //cmd.Parameters.AddWithValue("@PO_ID", PO_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_purchase_order()
            {

                Order_number = dr["ORDER_NO"].ToString(),
                PO_ID = Int32.Parse( dr["PO_ID"].ToString())
            });
        }

        return list;
    }

    public DataTable selectAll()
    {
        List<tbl_purchase_order> list = new List<tbl_purchase_order>();
        MySqlCommand cmd = new MySqlCommand();
     cmd.CommandText = "SELECT tpo.PO_ID,TPO.PURCHASE_ORDER_NO,tpo.USER_CODE as CUSTOMER,tcd.COMPANY_NAME, "
            + " CONCAT(TCD.COMPANY_ADDRESS,' ',CITY,'- ', POSTAL_CODE,',', STATE, ' ', COUNTRY)AS ADDRESS, "
            + " CONCAT(PRIMARY_TELEPHONE,',' ,SECONDARY_TELEPHONE,',',PRIMARY_MOBILE,',',SECONDARY_MOBILE) "
            + " AS CONTACT_DETAILS,CURRENCY_NAME AS CURRENCY, "
            + " CONCAT(PRODUCT_NAME ,'(',  CASE WHEN PURITY_TYPE IS NULL THEN '' ELSE PURITY_TYPE END , "
            + " CASE WHEN PSD_TYPE IS NULL THEN '' ELSE PSD_TYPE END , "
            + " CASE WHEN SURFACE_AREA_TYPE IS NULL THEN '' ELSE SURFACE_AREA_TYPE END ,')') AS PRODUCT_NAME, "
            + " MILL_TYPE, CASE WHEN IS_SAMPLE =0 THEN 'NO' ELSE 'YES' END AS IS_SAMPLE,QUANTITY,PRICE_PER_KG,PAYMENT_TERM, "
            + " DATE_FORMAT(DELIVERY_DATE,'%y%m%d') AS DELIVERY_DATE, "
            + " CASE WHEN PARTIAL_PAYMENT = 0 THEN 'NO' ELSE 'YES' END AS PARTIAL_PAYMENT, "
            + " DATE_FORMAT(production_relese_date,'%y%m%d') PRODUCTION_RELEASE_DATE, "
            + " SHIPPING_TYPE,SHIPPING_TERMS,DATE_FORMAT(TPO.CREATED_DATE,'%y%m%d') CREATED_DATE , TPO.CREATED_BY "
            + " FROM tbl_purchase_order tpo "
            + " JOIN tbl_client_details tcd ON tpo.user_code = tcd.user_code "
            + " JOIN tbl_sub_products tsm ON tpo.sub_product_id = tsm.sub_product_id "
            + " JOIN TBL_PRODUCT_MASTER TPM ON TSM.PRODUCT_ID = TPM.PRODUCT_ID "
            + " LEFT JOIN TBL_PURITY TP ON TSM.PURITY_ID = TP.PURITY_ID "
            + " left join tbl_currency_master tcum on tpo.currency_id = tcum.currency_id "
            + " LEFT JOIN TBL_PSD_TYPE TST ON TSM.PSD_ID = TST.PSD_ID "
            + " LEFT JOIN TBL_SURFACE_AREA TSA ON TSM.SURFACE_AREA_ID = TSA.SURFACE_AREA_ID "
            + " LEFT JOIN TBL_MILLS_MASTER TMM ON TSM.MILL_TYPE_ID = TMM.MILL_ID "
            + " JOIN TBL_PAYMENT_TERMS TPT ON TPO.PAYMENT_TERM_ID = TPT.PAYMENT_TERM_ID "
            + " JOIN TBL_SHIPPING_MASTER TSHM ON TPO.SHIPPING_ID = TSHM.SHIPPING_ID "
            + " JOIN TBL_SHIPPING_TERMS TSTS ON TPO.SHIPPING_TERMS_ID = TSTS.SHIPPING_TERMS_ID order by PO_ID DESC; ";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
        //foreach (DataRow dr in dt.Rows)
        //{
        //    list.Add(new tbl_purchase_order()
        //    {
        //        PO_ID = Convert.ToInt32(dr["PO_ID"]),
        //        USER_CODE = dr["USER_CODE"].ToString(),
        //        PURCHASE_ORDER_NO = dr["PURCHASE_ORDER_NO"].ToString(),
        //        IS_SAMPLE = Convert.ToInt32(dr["IS_SAMPLE"]),
        //        SUB_PRODUCT_ID = Convert.ToInt32(dr["SUB_PRODUCT_ID"]),
        //        QUANTITY = Convert.ToInt32(dr["QUANTITY"]),
        //        PRICE_PER_KG = Convert.ToInt32(dr["PRICE_PER_KG"]),
        //        PAYMENT_TERM_ID = Convert.ToInt32(dr["PAYMENT_TERM_ID"]),
        //        DELIVERY_DATE = dr["DELIVERY_DATE"].toDateTimeWithoutError(),
        //        PARTIAL_PAYMENT = Convert.ToInt32(dr["PARTIAL_PAYMENT"]),
        //        SHIPPING_ID = Convert.ToInt32(dr["SHIPPING_ID"]),
        //        SHIPPING_TERMS_ID = Convert.ToInt32(dr["SHIPPING_TERMS_ID"]),
        //        PRODUCTION_RELESE_DATE = dr["PRODUCTION_RELESE_DATE"].toDateTimeWithoutError(),
        //        CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
        //        CREATED_BY = dr["CREATED_BY"].ToString(),
        //        ACCEPTED_BY = dr["ACCEPTED_BY"].ToString(),
        //        ACCEPTED_DATE = dr["ACCEPTED_DATE"].toDateTimeWithoutError(),
        //        UPDATED_BY = dr["UPDATED_BY"].ToString(),
        //        UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
        //    });
        //}

       
    }

    public DataTable GetProducts()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "SELECT TSP.SUB_PRODUCT_ID,PRODUCT_NAME,"
                + " case when  PURITY_TYPE is null then 'N/A' else PURITY_TYPE end PURITY_TYPE, "
            + " case when  PSD_TYPE is null then 'N/A' Else PSD_TYPE end PSD_TYPE , "
            + " case when SURFACE_AREA_TYPE is null then 'N/A' else SURFACE_AREA_TYPE end SURFACE_AREA_TYPE,"
            + "  case when MILL_TYPE is null then 'N/A' else MILL_TYPE end MILL_TYPE ,STOCK_IN_KG CURRENT_STOCK "
                + " FROM TBL_SUB_PRODUCTS TSP "
                + " JOIN TBL_PRODUCT_MASTER TPM ON TSP.PRODUCT_ID = TPM.PRODUCT_ID "
                + " LEFT JOIN TBL_PURITY TP ON TSP.PURITY_ID = TP.PURITY_ID "
                + " LEFT JOIN TBL_PSD_TYPE TPT ON TSP.PSD_ID = TPT.PSD_ID "
                + " LEFT JOIN TBL_SURFACE_AREA TSA ON TSP.SURFACE_AREA_ID = TSA.SURFACE_AREA_ID "
                + " LEFT JOIN TBL_MILLS_MASTER TMM ON TSP.MILL_TYPE_ID = TMM.MILL_ID;";

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetClients()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = " SELECT USER_CODE , COMPANY_NAME , "
            + " CONCAT(COMPANY_ADDRESS, ',', CITY,'</BR>', "
            + " STATE , '(', COUNTRY , ')' , '-' , POSTAL_CODE, "
            + " '</BR> PRIMARY CONTACT : ' , PRIMARY_MOBILE , ',', PRIMARY_TELEPHONE, "
            + " '</BR> SECONDARY CONTACT : ' , SECONDARY_MOBILE, ',', SECONDARY_TELEPHONE "
            + " )ADDRESS "
            + " FROM TBL_CLIENT_DETAILS WHERE ACTIVE =1 order by COMPANY_NAME ";

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetClients(string User_code)
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = " SELECT USER_CODE , COMPANY_NAME , "
            + " CONCAT(COMPANY_ADDRESS, ',', CITY,'</BR>', "
            + " STATE , '(', COUNTRY , ')' , '-' , POSTAL_CODE, "
            + " '</BR> PRIMARY CONTACT : ' , PRIMARY_MOBILE , ',', PRIMARY_TELEPHONE, "
            + " '</BR> SECONDARY CONTACT : ' , SECONDARY_MOBILE, ',', SECONDARY_TELEPHONE, "
            + " '</br>' "
            + " )ADDRESS "
            + " FROM TBL_CLIENT_DETAILS WHERE ACTIVE =1 and User_Code = '" + User_code +"'";

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetPaymentTerms()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = " SELECT PAYMENT_TERM_ID, PAYMENT_TERM  FROM tbl_payment_terms "
            + " WHERE IS_ACTIVE =1 ORDER BY PAYMENT_TERM ";

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetShippingType()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = " SELECT SHIPPING_ID, SHIPPING_TYPE FROM TBL_SHIPPING_MASTER "
            + " WHERE IS_ACTIVE = 1 ORDER BY SHIPPING_TYPE ";

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetShippingTerms()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = " SELECT SHIPPING_TERMS_ID, SHIPPING_TERMS FROM TBL_SHIPPING_TERMS " 
            + " WHERE IS_ACTIVE =1 ORDER BY SHIPPING_TERMS";

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }


    public int insert(int PO_ID, String USER_CODE, String PURCHASE_ORDER_NO, int IS_SAMPLE, int SUB_PRODUCT_ID, double QUANTITY, double PRICE_PER_KG, int PAYMENT_TERM_ID, DateTime DELIVERY_DATE, int PARTIAL_PAYMENT, int SHIPPING_ID, int SHIPPING_TERMS_ID, DateTime PRODUCTION_RELESE_DATE, DateTime CREATED_DATE, String CREATED_BY, String ACCEPTED_BY, DateTime ACCEPTED_DATE, int CURRENCY_ID)
    {
        int i = 0;
        string sql = @"Insert into tbl_purchase_order(PO_ID, USER_CODE, PURCHASE_ORDER_NO, IS_SAMPLE, SUB_PRODUCT_ID, QUANTITY, PRICE_PER_KG, PAYMENT_TERM_ID, DELIVERY_DATE, PARTIAL_PAYMENT, SHIPPING_ID, SHIPPING_TERMS_ID, PRODUCTION_RELESE_DATE, CREATED_DATE, CREATED_BY, ACCEPTED_BY, ACCEPTED_DATE,CURRENCY_ID)
                        values(@PO_ID, @USER_CODE, @PURCHASE_ORDER_NO, @IS_SAMPLE, @SUB_PRODUCT_ID, @QUANTITY, @PRICE_PER_KG, @PAYMENT_TERM_ID, @DELIVERY_DATE, @PARTIAL_PAYMENT, @SHIPPING_ID, @SHIPPING_TERMS_ID, @PRODUCTION_RELESE_DATE, @CREATED_DATE, @CREATED_BY, @ACCEPTED_BY, @ACCEPTED_DATE,@CURRENCY_ID)";

        MySqlCommand cmd = new MySqlCommand(sql);

         cmd.Parameters.AddWithValue("@PO_ID", PO_ID);
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@PURCHASE_ORDER_NO", PURCHASE_ORDER_NO);
        cmd.Parameters.AddWithValue("@IS_SAMPLE", IS_SAMPLE);
        cmd.Parameters.AddWithValue("@SUB_PRODUCT_ID", SUB_PRODUCT_ID);
        cmd.Parameters.AddWithValue("@QUANTITY", QUANTITY);
        cmd.Parameters.AddWithValue("@PRICE_PER_KG", PRICE_PER_KG);
        cmd.Parameters.AddWithValue("@PAYMENT_TERM_ID", PAYMENT_TERM_ID);
        cmd.Parameters.AddWithValue("@DELIVERY_DATE", DELIVERY_DATE);
        cmd.Parameters.AddWithValue("@PARTIAL_PAYMENT", PARTIAL_PAYMENT);
        cmd.Parameters.AddWithValue("@SHIPPING_ID", SHIPPING_ID);
        cmd.Parameters.AddWithValue("@SHIPPING_TERMS_ID", SHIPPING_TERMS_ID);
        cmd.Parameters.AddWithValue("@PRODUCTION_RELESE_DATE", PRODUCTION_RELESE_DATE);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@ACCEPTED_BY", ACCEPTED_BY);
        cmd.Parameters.AddWithValue("@ACCEPTED_DATE", ACCEPTED_DATE);
        cmd.Parameters.AddWithValue("@CURRENCY_ID", CURRENCY_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public int ApprovePo(int PO_ID, string UserCode)
    {
        int i = 0;
        string sql = " UPDATE tbl_purchase_order SET Accepted_by = '" + UserCode + "'" + ", Accepted_date ='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'"
            + " where PO_ID =" + PO_ID.ToString()   ;
        MySqlCommand cmd = new MySqlCommand(sql);
        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public int update(int PO_ID, String USER_CODE, String PURCHASE_ORDER_NO, int IS_SAMPLE, int SUB_PRODUCT_ID, double QUANTITY, double PRICE_PER_KG, int PAYMENT_TERM_ID, DateTime DELIVERY_DATE, int PARTIAL_PAYMENT, int SHIPPING_ID, int SHIPPING_TERMS_ID, DateTime PRODUCTION_RELESE_DATE, String ACCEPTED_BY, DateTime ACCEPTED_DATE, String UPDATED_BY, DateTime UPDATED_DATE, int CURRENCY_ID)
    {
        int i = 0;
        string sql = @"Update tbl_purchase_order SET @PO_ID=PO_ID, @USER_CODE=USER_CODE, @PURCHASE_ORDER_NO=PURCHASE_ORDER_NO, @IS_SAMPLE=IS_SAMPLE, @SUB_PRODUCT_ID=SUB_PRODUCT_ID, @QUANTITY=QUANTITY, @PRICE_PER_KG=PRICE_PER_KG, @PAYMENT_TERM_ID=PAYMENT_TERM_ID, @DELIVERY_DATE=DELIVERY_DATE, @PARTIAL_PAYMENT=PARTIAL_PAYMENT, @SHIPPING_ID=SHIPPING_ID, @SHIPPING_TERMS_ID=SHIPPING_TERMS_ID, @PRODUCTION_RELESE_DATE=PRODUCTION_RELESE_DATE, @ACCEPTED_BY=ACCEPTED_BY, @ACCEPTED_DATE=ACCEPTED_DATE, @UPDATED_BY=UPDATED_BY, @UPDATED_DATE=UPDATED_DATE, CURRENCY_ID=@CURRENCY_ID  WHERE PO_ID=@PO_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@PO_ID", PO_ID);
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@PURCHASE_ORDER_NO", PURCHASE_ORDER_NO);
        cmd.Parameters.AddWithValue("@IS_SAMPLE", IS_SAMPLE);
        cmd.Parameters.AddWithValue("@SUB_PRODUCT_ID", SUB_PRODUCT_ID);
        cmd.Parameters.AddWithValue("@QUANTITY", QUANTITY);
        cmd.Parameters.AddWithValue("@PRICE_PER_KG", PRICE_PER_KG);
        cmd.Parameters.AddWithValue("@PAYMENT_TERM_ID", PAYMENT_TERM_ID);
        cmd.Parameters.AddWithValue("@DELIVERY_DATE", DELIVERY_DATE);
        cmd.Parameters.AddWithValue("@PARTIAL_PAYMENT", PARTIAL_PAYMENT);
        cmd.Parameters.AddWithValue("@SHIPPING_ID", SHIPPING_ID);
        cmd.Parameters.AddWithValue("@SHIPPING_TERMS_ID", SHIPPING_TERMS_ID);
        cmd.Parameters.AddWithValue("@PRODUCTION_RELESE_DATE", PRODUCTION_RELESE_DATE);
        cmd.Parameters.AddWithValue("@ACCEPTED_BY", ACCEPTED_BY);
        cmd.Parameters.AddWithValue("@ACCEPTED_DATE", ACCEPTED_DATE);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);
        cmd.Parameters.AddWithValue("@CURRENCY_ID", CURRENCY_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public int delete(int PO_ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_purchase_order WHERE PO_ID=@PO_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@PO_ID", PO_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public bool CheckPurchaseOrder(string POrder)
    {
      
        DataTable dt = new DataTable();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = " select 1 from tbl_purchase_order where PURCHASE_ORDER_NO ='" + POrder + "'" ;
        dt = DBUtil.ExecuteDataTable(cmd);

        if (dt.Rows.Count > 0) return true;       
        return false;
    }

    public DataTable FetchPendingApproval()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandTimeout = 0;

            cmd.CommandText = "SELECT PO_ID, " 
		    + " PURCHASE_ORDER_NO, CASE WHEN IS_SAMPLE = 1 THEN 'Yes' ELSE 'No' END  IS_SAMPLE, "
		    + " CASE WHEN company_name IS NULL THEN '' ELSE company_name END  company_name, "
		    + " CASE WHEN  Primary_Mobile IS NULL THEN '' ELSE Primary_Mobile END Primary_Mobile  ,  "
		+ " CASE WHEN primary_telephone IS NULL THEN '' ELSE CONCAT('/',primary_telephone) END primary_telephone "
		+ ", CASE WHEN Company_address IS NULL THEN '' ELSE Company_address END Company_address ,"
		+ " CASE WHEN city IS NULL THEN '' ELSE city END city ,"
		+ " CASE WHEN state IS NULL THEN '' ELSE state END state, "
		+ " CASE WHEN country IS NULL THEN '' ELSE country END country , "
		+ " CASE WHEN postal_code IS NULL THEN '' ELSE postal_code END  postal_code , "
		+ " CASE WHEN Product_Name IS NULL THEN '' ELSE Product_Name END Product_Name , "
        + "	CASE WHEN Purity_type IS NULL THEN '' ELSE Purity_type END Purity_type, "
        + " CASE WHEN  PSD_TYPE IS NULL THEN '' ELSE psd_type END PSD_TYPE, "
		+ " CASE WHEN  Surface_area_type IS NULL THEN '' ELSE surface_area_type END Surface_area_type, "
		+ "	CASE WHEN  mill_type IS NULL THEN  '' ELSE  mill_type END  mill_type, "
		+ " CASE WHEN  stock_in_kg IS NULL THEN 0 ELSE stock_in_kg END stock_in_kg  "
		
        + "	FROM tbl_purchase_order tpo "
		+ " JOIN tbl_client_details tcd ON tpo.user_code = tcd.user_code "
		+ " JOIN tbl_sub_products tsp ON tpo.SUB_PRODUCT_ID = tsp.SUB_PRODUCT_ID  "
		+ " LEFT JOIN tbl_product_master tpm ON tsp.product_id = tpm.product_id "
		+ " LEFT JOIN tbl_purity tp ON tsp.purity_id = tp.purity_id "
		+ " LEFT JOIN tbl_psd_type tpt ON tsp.psd_id = tpt.psd_id "
		+ " LEFT JOIN tbl_surface_area tsa ON tsp.surface_area_id = tsa.surface_area_id "
		+ " LEFT  JOIN tbl_mills_master tmm ON tsp.mill_type_id  = tmm.mill_id  "
		+ " WHERE accepted_by IS NULL";



            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }


    public DataTable getWorkOrderNum()
    {



        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "select * from tbl_purchase_order";
        

        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }

}